package com.cx.healthex.base.dao.util;

import java.io.File;
import java.io.UnsupportedEncodingException;
import java.net.URL;
import java.net.URLDecoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;

import com.cx.healthex.base.dao.util.SqlUtil;

public class SqlUtil {

	static String parentName = "opt";
	static Map<String, Document> docMap = new HashMap<String, Document>();
	static {
		URL url = SqlUtil.class.getClassLoader().getResource("storedSQL");
		try {
			String path = URLDecoder.decode(url.getPath(), "UTF-8");
			File file=new File(path);
			File[] tempList = file.listFiles();
			for (int i = 0; i < tempList.length; i++) {
				if (tempList[i].isFile() && tempList[i].getName().indexOf(".xml") >= 0) {
					String pathTemp = path +"/"+ tempList[i].getName();
					SAXReader reader = new SAXReader();
					Document doc = reader.read(new File(pathTemp));
					docMap.put(tempList[i].getName().replace(".xml", ""), doc);
				}
			}
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
			System.out.println("加载通用查询sql出错01");
		} catch (DocumentException e) {
			e.printStackTrace();
			System.out.println("加载通用查询sql出错02");
		}
	}
	
	
	/**
	 * 根据编号查询sql
	 * */
	public String findSqlByStoredSQL(String fileName,String sqlNo){
		String sql = "";
		Element root = null;
		Document doc = docMap.get(fileName);
		if (doc != null) {
			root = doc.getRootElement();
		}
		Element sqlNode = null;
		if (root != null) {
			sqlNode = root.element(parentName);
		}
		if (sqlNode != null) {
			sql = sqlNode.elementTextTrim(sqlNo);
		}
		return sql;
	}
	
	/**
	 * sql处理 与 传参封装
	 * */
	public HashMap<String, Object> finishSqlAndparameter(String sql,HashMap<String, Object> paramMap){
		HashMap<String, Object> result = new HashMap<String, Object>();
		List<Object> param = new ArrayList<Object>();
		//获取sql
		if(sql != null && !"".equals(sql)){
			while(sql.indexOf("#") > 0){
				String name = sql.substring(sql.indexOf("#")+1, sql.indexOf("#",sql.indexOf("#")+1));
				String condition = sql.substring(sql.indexOf("["), sql.indexOf("]")+1);
				Object obj = paramMap.get(name);
				if(obj != null && obj.toString() != null && !"".equals(obj.toString()) && !"".equals(obj+"") && !"null".equals(obj.toString()) && !"null".equals(obj+"") && !"undefined".equals(obj.toString()) && !"undefined".equals(obj+"")){
					/****in查询****/
					if(condition.indexOf(" in ") > 0  || condition.indexOf(" IN ") > 0){
						Object[] objs = null;
						try {
							objs = (Object[])paramMap.get(name);
						} catch (Exception e) {
							e.printStackTrace();
						}
						
						if(objs != null && objs.length > 0){
							sql = sql.replaceFirst("#"+name+"#", getMark(objs.length));
							for(Object o : objs){
								param.add(o);
							}
							sql = sql.replaceFirst("\\[", " ");
							sql = sql.replaceFirst("\\]", " ");
						}else{
							//如果传参为空则清除该查询条件
							String condition2 = sql.substring(sql.indexOf("["), sql.indexOf("]")+1);
							sql = sql.replace(condition2, " ");
						}
					}else{
						/****模糊查询****/
						if(condition.indexOf(" like ") > 0  || condition.indexOf(" LIKE ") > 0){
							sql = sql.replaceFirst("#"+name+"#", "?");
							param.add("%"+paramMap.get(name).toString()+"%");
						}else{
							sql = sql.replaceFirst("#"+name+"#", "?");
							param.add(paramMap.get(name));
						}
						sql = sql.replaceFirst("\\[", " ");
						sql = sql.replaceFirst("\\]", " ");
					}
				}else{
					String condition2 = sql.substring(sql.indexOf("["), sql.indexOf("]")+1);
					sql = sql.replace(condition2, " ");
				}
			}
			sql = sql.replace("[", " ");
			sql = sql.replace("]", " ");
			
			result.put("sql", sql);
			result.put("parameter", param);
		}
		
		//System.out.println(sql);
		//System.out.println(param.toArray());
		
		return result;
		
	}
	
	public String getMark(Integer len){
		String str  = "";
		if(len != null && len > 0){
			for(;len>0;len--){
				str += "?,";
			}
			str = str.substring(0, str.length()-1);
		}
		return str;
	}
}
